﻿using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using YanZhiwei.DotNet2.Interfaces.DataAccess;
namespace YanZhiwei.DotNet2.Utilities.Common
{
    /// <summary>
    /// MS SQL帮助类
    /// </summary>
    public class MSSQLHelper : ISQLHelper
    {
        #region 构造函数以及私有变量
        static string connectionString = string.Empty;
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="msconString">连接字符串
        /// <example>eg:string _sqlConnectString = @"server=YANZHIWEI-PC\SQLEXPRESS;database=db;uid=sa;pwd=sasa;";</example>
        /// </param>
        public MSSQLHelper(string msconString)
        {
            connectionString = msconString;
        }
        #endregion
        #region 执行sql语句，返回影响行数
        /// <summary>
        /// 执行sql语句，返回影响行数
        ///<para>eg: string _sql = "insert into [Person](PName,PAge,PAddress) values(@pname,@page,@paddress)";</para>   
        ///<para>int _actual = SqlHelper.ExecuteNonQuery(_sql,</para>
        ///<para>new DbParameter[3] {</para>
        ///<para>new SqlParameter("@pname","YanZhiwei"),</para>
        ///<para>new SqlParameter("@page",18),</para>
        ///<para>new SqlParameter("@paddress","zhuzhou")</para>
        ///<para>});</para>
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="parameters">参数</param>
        /// <returns>影响行数</returns>
        public int ExecuteNonQuery(string sql, DbParameter[] parameters)
        {
            int _affectedRows = -1;
            using (SqlConnection sqlcon = new SqlConnection(connectionString))
            {
                sqlcon.Open();
                using (SqlCommand sqlcmd = new SqlCommand(sql, sqlcon))
                {
                    if (parameters != null)
                        sqlcmd.Parameters.AddRange(parameters);
                    _affectedRows = sqlcmd.ExecuteNonQuery();
                }
            }
            return _affectedRows;
        }
        #endregion
        #region ExecuteReader
        /// <summary>
        /// ExecuteReader
        /// <para>eg:string _sql = "select * from dbo.Person where PName=@pname";</para>
        /// <para>IDataReader _reader = SqlHelper.ExecuteReader(_sql, new DbParameter[1] { new SqlParameter("@pname", "YanZhiwei") });</para>
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="parameters">参数</param>
        /// <returns>IDataReader</returns>
        public IDataReader ExecuteReader(string sql, DbParameter[] parameters)
        {
            SqlConnection _sqlcon = new SqlConnection(connectionString);
            using (SqlCommand _sqlcmd = new SqlCommand(sql, _sqlcon))
            {
                if (parameters != null)
                    _sqlcmd.Parameters.AddRange(parameters);
                _sqlcon.Open();
                return _sqlcmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
        }
        #endregion
        #region ExecuteDataTable
        /// <summary>
        /// ExecuteDataTable
        /// <para>eg:string _sql = "select * from dbo.Person where PName=@pname";</para>
        /// <para>DataTable _table = SqlHelper.ExecuteDataTable(_sql, new DbParameter[1] { new SqlParameter("@pname", "YanZhiwei") });</para>
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="parameters">参数</param>
        /// <returns>DataTable</returns>
        public DataTable ExecuteDataTable(string sql, DbParameter[] parameters)
        {
            using (SqlConnection _sqlcon = new SqlConnection(connectionString))
            {
                using (SqlCommand _sqlcmd = new SqlCommand(sql, _sqlcon))
                {
                    if (parameters != null)
                        _sqlcmd.Parameters.AddRange(parameters);
                    using (SqlDataAdapter _sqldap = new SqlDataAdapter(_sqlcmd))
                    {
                        DataTable _dt = new DataTable();
                        _sqldap.Fill(_dt);
                        return _dt;
                    }
                }
            }
        }
        #endregion
        #region ExecuteScalar
        /// <summary>
        /// ExecuteScalar
        /// <para>eg:string _sql = "select PAge from dbo.Person where PName=@pname";</para>
        /// <para>object _value = SqlHelper.ExecuteScalar(_sql, new DbParameter[1] { new SqlParameter("@pname", "YanZhiwei") });</para>
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="parameters">参数</param>
        /// <returns>Object</returns>
        public Object ExecuteScalar(string sql, DbParameter[] parameters)
        {
            using (SqlConnection _sqlcon = new SqlConnection(connectionString))
            {
                using (SqlCommand _sqlcmd = new SqlCommand(sql, _sqlcon))
                {
                    if (parameters != null)
                        _sqlcmd.Parameters.AddRange(parameters);
                    _sqlcon.Open();
                    return _sqlcmd.ExecuteScalar();
                }
            }
        }
        #endregion
        #region BatchInert批量插入
        /// <summary>
        /// 批量插入
        /// <para>eg:int _actual = SqlHelper.BatchInert("Person", _db, 300);</para>
        /// </summary>
        /// <param name="desTable">数据库目标表</param>
        /// <param name="dt">需要插入的表</param>
        /// <param name="batchSize">批量插入数量</param>
        /// <returns>影响行数</returns>
        public int BatchInert(string desTable, DataTable dt, int batchSize)
        {
            using (SqlBulkCopy sbc = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction)
            {
                BulkCopyTimeout = 300,
                NotifyAfter = dt.Rows.Count,
                BatchSize = batchSize,
                DestinationTableName = desTable
            })
            {
                foreach (DataColumn column in dt.Columns)
                    sbc.ColumnMappings.Add(column.ColumnName, column.ColumnName);
                sbc.WriteToServer(dt);
            }
            return dt.Rows.Count;
        }
        #endregion
        #region  ExecuteReader——存储过程
        /// <summary>
        /// ExecuteReader——存储过程
        /// <para>eg:SqlParameter _parameter = new SqlParameter("@pName", SqlDbType.NVarChar);</para>>
        /// <para>   _parameter.Direction = ParameterDirection.Input;</para>
        /// <para>   _parameter.Value = "YanZhiwei";</para>
        /// <para>   DbParameter[] _parameterList = new DbParameter[1] { _parameter };</para>
        /// <para>   IDataReader _reader = SqlHelper.ExecuteReader_SP("PROC_FilterPerson", _parameterList);</para>
        /// </summary>
        /// <param name="proName">存储过程名称</param>
        /// <param name="parameters">参数</param>
        /// <returns>IDataReader</returns>
        public IDataReader ExecuteReader_SP(string proName, DbParameter[] parameters)
        {
            SqlConnection _sqlcon = new SqlConnection(connectionString);
            using (SqlCommand _sqlcmd = new SqlCommand())
            {
                _sqlcmd.Connection = _sqlcon;
                _sqlcmd.CommandType = CommandType.StoredProcedure;
                _sqlcmd.CommandText = proName;
                if (parameters != null)
                    _sqlcmd.Parameters.AddRange(parameters);
                _sqlcon.Open();
                return _sqlcmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
        }
        #endregion 
    }
}